package krao.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;

import krao.test.QueryParserTest;

import org.apache.log4j.Logger;

public class DBProvider {
	private final static Logger logger = Logger.getLogger(QueryParserTest.class);
	/** Connection URL . */
	private static final String CONNECTION_URL;

	/** Connection login to the DB. */
	private static final String CONNECTION_LOGIN;

	/** Connection password to the DB. */
	private static final String CONNECTION_PWD;

	/** Used to execute all commands to derby db */
	private static Statement stat;
	private static Connection conn;

	static {
		CONNECTION_URL = "jdbc:derby:test;create=true";
		CONNECTION_LOGIN = "root";
		CONNECTION_PWD = "root123";

		try {
			conn = DriverManager.getConnection(CONNECTION_URL, CONNECTION_LOGIN,
					CONNECTION_PWD);
			stat = conn.createStatement();
		} catch (SQLException e) {
			logger.error("Unable to create in-memory derby database");
			logger.error("e");
		}
	}

	public static HashSet<String> getPresentTables() {
		HashSet<String> result = new HashSet<String>();
		try {
			// Specify the type of object; in this case we want tables
			String[] types = { "TABLE" };
			ResultSet resultSet = conn.getMetaData().getTables(null, null, "%", types);

			if (resultSet != null) {
				// Get the table names
				while (resultSet.next()) {
					// Get the table name
					result.add(resultSet.getString(3));
				}
			}
		} catch (SQLException e) {
			logger.error(e);
		}
		return result;
	}

	public static void dropTables() {
		HashSet<String> hs = getPresentTables();

		for (String table : hs) {
			try {
				stat.execute("DROP TABLE " + table);
			} catch (SQLException e) {
				logger.error(e);
			}
		}
	}

	public static void executeUpdate(String query) {
		try {
			stat.executeUpdate(query);
		} catch (SQLException e) {
			logger.error(e);
		}
	}

	public static ResultSet executeQuery(String query) {
		try {
			return stat.executeQuery(query);
		} catch (SQLException e) {
			logger.error("Error executing query:\'" + query + "\'\n" + e);
		}
		return null;
	}

	public static String resultSetToString(ResultSet rs) {
		if (rs == null) {
			return "Empty ResultSet";
		}
		StringBuilder sb = new StringBuilder("\n");
		try {
			// Get result set meta data
			ResultSetMetaData rsmd = rs.getMetaData();
			int numColumns = rsmd.getColumnCount();
			int numRows = 0;
			logger.info("Result set columns: " + numColumns);
			// make header
			for (int i = 1; i < numColumns + 1; i++) {
				sb.append(String.format("%s\t", rsmd.getColumnName(i)));
			}
			sb.append("\n");

			// for each result
			while (rs.next()) {
				numRows++;
				for (int i = 1; i < numColumns + 1; i++) {
					sb.append(String.format("%s\t", rs.getObject(i).toString()));
				}
				sb.append("\n");
			}
			logger.info("Result set rows: " + numRows);
		} catch (SQLException e) {
			logger.error(e);
		}
		return sb.toString();
	}
}
